﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace DBCreator
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.BackgroundColor = ConsoleColor.Black;

            var location = System.Reflection.Assembly.GetExecutingAssembly().Location;
            var pat = Path.GetDirectoryName(location);
            var dbPath = Path.Combine(pat, "DB", "Radar.mdf");

            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine($"\n即将创建 {dbPath}");

            try
            {
                if (!File.Exists(dbPath)) // mdf文件不存在则创建
                {
                    if (!Directory.Exists(Path.GetDirectoryName(dbPath)))
                    {
                        Directory.CreateDirectory(System.IO.Path.GetDirectoryName(dbPath));
                    }
                    StringBuilder sb = new StringBuilder();
                    sb.Append("CREATE DATABASE [Radar]");
                    sb.Append("On");
                    sb.Append($"( NAME='Radar', FILENAME ='{dbPath}')");//默认自增长是64M,默认最大大小是不限制大小
                    var dbLogPath = Path.Combine(pat, "DB", "Radar_log.ldf");
                    if (File.Exists(dbLogPath))
                    {
                        try
                        {
                            File.Delete(dbLogPath);
                        }
                        catch (IOException)
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine("\nRadar_log.ldf 文件已存在，且正在被占用，请手动删除Radar_log.ldf文件后重试");
                            goto exit;
                        }
                        catch (UnauthorizedAccessException)
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine("\nRadar_log.ldf 文件已存在，且删除失败，请手动删除Radar_log.ldf文件后重试");
                            goto exit;
                        }
                        catch { }
                        Console.ForegroundColor = ConsoleColor.White;
                    }
                    sb.Append("LOG ON ");
                    sb.Append($"( NAME='Radar_log', FILENAME ='{dbLogPath}' )");
                    var createDBSql = sb.ToString();

                    using (SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB"))
                    {
                        try
                        {
                            connection.Open();
                        }
                        catch (Exception ex)
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine($"\n 连接LocalDB失败:{ex}");
                            Console.WriteLine($"请检查是否正常安装LocabDB工具\n");
                            Console.ForegroundColor = ConsoleColor.White;
                            goto exit;
                        }

                        using (SqlCommand command = new SqlCommand("DROP DATABASE IF EXISTS [Radar]", connection))
                        {
                            // 处理 数据库mdf文件被删，但是数据信息还存放在localDB 实例中，导致创建失败的情况
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                Console.ForegroundColor = ConsoleColor.DarkYellow;
                                Console.WriteLine(ex);
                                Console.ForegroundColor = ConsoleColor.White;
                            }
                        }

                        using (SqlCommand command = new SqlCommand(createDBSql, connection))
                        {
                            Console.ForegroundColor = ConsoleColor.White;
                            Console.WriteLine("\n 开始创建数据库...");
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                Console.ForegroundColor = ConsoleColor.Red;
                                Console.WriteLine($"\n 无法创建数据库:{ex}\n");
                                Console.ForegroundColor = ConsoleColor.White;
                                goto exit;
                            }
                        }
                        connection.Close();
                    }

                    // create Table
                    var conStr = $@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename ={dbPath}; Integrated Security = True";
                    using (SqlConnection connection = new SqlConnection(conStr))
                    {
                        try
                        {
                            connection.Open();
                        }
                        catch (Exception ex)
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine($"\n打开数据库失败:{ex}");
                            Console.ForegroundColor = ConsoleColor.White;
                            goto exit;
                        }
                        Console.ForegroundColor = ConsoleColor.White;
                        // 不存在则创建
                        var createCiMISS = @"IF OBJECT_ID('dbo.Table_BaseCimiss', 'U') IS NULL
                                            CREATE TABLE [dbo].[Table_BaseCimiss] (
                                            [Id]         INT      IDENTITY (1, 1) NOT NULL,
                                            [Time]       DATETIME CONSTRAINT [DF_Table_BaseCimiss_Time] DEFAULT (getdate()) NULL,
                                            [ResultCode] INT      DEFAULT ((0)) NOT NULL,
                                            PRIMARY KEY CLUSTERED ([Id] ASC)
                                          ) ";
                        using (SqlCommand command = new SqlCommand(createCiMISS, connection))
                        {
                            Console.WriteLine("\n    创建表：Table_BaseCimiss...");
                            command.ExecuteNonQuery();
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine("    Table_BaseCimiss 创建成功");
                            Console.ForegroundColor = ConsoleColor.White;
                        }

                        // 
                        var createBaseFtp = @"IF OBJECT_ID('dbo.Table_BaseFtp', 'U') IS NULL
                                            CREATE TABLE [dbo].[Table_BaseFtp] (
                                            [Id]         INT            IDENTITY (1, 1) NOT NULL,
                                            [Time]       DATETIME       CONSTRAINT [DF_Table_BaseFtp_Time] DEFAULT (getdate()) NULL,
                                            [LastedFile] TEXT NULL,
                                            [ResultCode] INT            DEFAULT ((0)) NOT NULL,
                                            PRIMARY KEY CLUSTERED ([Id] ASC)
                                        )"; 
                        using (SqlCommand command = new SqlCommand(createBaseFtp, connection))
                        {
                            Console.WriteLine("\n    创建表：Table_BaseFtp...");
                            command.ExecuteNonQuery();
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine("    表Table_BaseFtp 创建成功");
                            Console.ForegroundColor = ConsoleColor.White;
                        }

                        var createNet = @" IF OBJECT_ID('dbo.Table_Net', 'U') IS NULL
                                            CREATE TABLE [dbo].[Table_Net] (
                                            [Id]         INT      IDENTITY (1, 1) NOT NULL,
                                            [Time]       DATETIME CONSTRAINT [DF_Table_Table_Net_Time] DEFAULT (getdate()) NULL,
                                            [ResultCode] INT      DEFAULT ((0)) NOT NULL,
                                            PRIMARY KEY CLUSTERED ([Id] ASC)
                                        )";
                        using (SqlCommand command = new SqlCommand(createNet, connection))
                        {
                            Console.WriteLine("\n    创建表：Table_Net...");
                            command.ExecuteNonQuery();
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine("    表Table_Net 创建成功");
                            Console.ForegroundColor = ConsoleColor.White;
                        }

                        var createMonidor = @" IF OBJECT_ID('dbo.Table_Monidor', 'U') IS NULL
                                            CREATE TABLE [dbo].[Table_Monidor] (
                                            [Id]        INT            IDENTITY (1, 1) NOT NULL,
                                            [Source]    INT            NULL,
                                            [Time]      DATETIME       CONSTRAINT [DF_Table_Monidor_Time] DEFAULT (getdate()) NULL,
                                            [AlarmCode] INT            DEFAULT ((0)) NOT NULL,
                                            [Msg]       VARCHAR(256) NULL,
                                            PRIMARY KEY CLUSTERED ([Id] ASC)
                                        )";
                        using (SqlCommand command = new SqlCommand(createMonidor, connection))
                        {
                            Console.WriteLine("\n    创建表：Table_Monidor...");
                            command.ExecuteNonQuery();
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine("    表Table_Monidor 创建成功");
                            Console.ForegroundColor = ConsoleColor.White;
                        }


                        var createProduct = @" IF OBJECT_ID('dbo.Table_Products', 'U') IS NULL
                                            CREATE TABLE [dbo].[Table_Products] (
                                            [Id]         INT            IDENTITY (1, 1) NOT NULL,
                                            [Time]       DATETIME       CONSTRAINT [DF_Table_Products_Time] DEFAULT (getdate()) NULL,
                                            [LastedFile] TEXT NULL,
                                            [ResultCode] INT            DEFAULT ((0)) NOT NULL,
                                            PRIMARY KEY CLUSTERED ([Id] ASC)
                                        )";
                        using (SqlCommand command = new SqlCommand(createProduct, connection))
                        {
                            Console.WriteLine("\n    创建表：Table_Products...");
                            command.ExecuteNonQuery();
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine("    Table_Products 创建成功");
                            Console.ForegroundColor = ConsoleColor.White;
                        }



                        var createRDA = @" IF OBJECT_ID('dbo.Table_Rda', 'U') IS NULL
                                           CREATE TABLE [dbo].[Table_Rda] (
                                            [Id]          INT            IDENTITY (1, 1) NOT NULL,
                                            [Time]        DATETIME       CONSTRAINT [DF_Table_Rda_Time] DEFAULT (getdate()) NULL,
                                            [Code]        INT            DEFAULT ((0)) NULL,
                                            [Description] TEXT NULL,
                                            PRIMARY KEY CLUSTERED ([Id] ASC)
                                        )";
                        using (SqlCommand command = new SqlCommand(createRDA, connection))
                        {
                            Console.WriteLine("\n    创建表：Table_Rda...");
                            command.ExecuteNonQuery();
                            Console.ForegroundColor = ConsoleColor.Green;
                            Console.WriteLine("    Table_Rda 创建成功");
                            Console.ForegroundColor = ConsoleColor.White;
                        }











                        Console.ForegroundColor = ConsoleColor.Green;
                        Console.WriteLine("\n数据库创建成功");
                        Console.ForegroundColor = ConsoleColor.White;
                        connection.Close();
                    }
                }
                else
                {
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine("\n文件已存在，创建终止。");
                    Console.ForegroundColor = ConsoleColor.White;
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"创建失败:{ex}");
                Console.ForegroundColor = ConsoleColor.White;
            }
        exit:
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine("\n按任意键并回车 退出程序");
            Console.Read();

        }
    }
}

